package ar.republicagroup.reportes;

import ar.republicagroup.reportes.bean.ReporteComGestionLiquidBean;
import ar.republicagroup.reportes.util.ConnectionDB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Reporte{
    
        
        private static org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(App.class);
    
        public List<ReporteComGestionLiquidBean> reporteComisionGestionPorLiquid(String fechaLiquidacion)
        {
          Connection cn = null;
          ResultSet rs = null;
          PreparedStatement pst = null;
          List<ReporteComGestionLiquidBean>listReporte = new ArrayList();

          log.info("fecha Liquidacion : "+fechaLiquidacion);

          String query = "SELECT cli.nombre,ROUND(SUM(CASE WHEN che.cheque_estado_id IN (7,3,4,5)  "
                  + "THEN (che.importe * che.comision/100) END),2) as comision_gestion,ROUND(SUM(CASE WHEN che.cheque_estado_id IN (3,4,5)  "
                  + "THEN (SELECT comision FROM cheque_penalizacion "
                  + "WHERE cheque_penalizacion.cheque_estado_id = che.cheque_estado_id) END),2) as rechazos "
                  + "FROM liquidacion liq INNER JOIN liquidacion_detalle dliq ON liq.id = dliq.liquidacion_id "
                  + "INNER JOIN cheque che ON che.id = dliq.cheque_id "
                  + "INNER JOIN cheque_estado ce ON che.cheque_estado_id = ce.id "
                  + "LEFT JOIN cheque_penalizacion cp ON ce.id = cp.cheque_estado_id "
                  + "INNER JOIN bolsa_cheque bc ON che.bolsa_cheque_id =  bc.id "
                  + "INNER JOIN cliente cli ON cli.id = bc.cliente_id "
                  + "WHERE che.cheque_estado_id IN (7,3,4,5) "
                  + "AND DATE(liq.fecha_registro) LIKE ? "
                  + "AND che.estado = 1 GROUP BY cli.nombre;";
          try
          {
            cn = ConnectionDB.getInstance().getConnection();
            pst = cn.prepareStatement(query);
            pst.setString(1, fechaLiquidacion);
            rs = pst.executeQuery();
            while (rs.next())
            {
              ReporteComGestionLiquidBean reporteBean = new ReporteComGestionLiquidBean();
              reporteBean.setNombreCliente(rs.getString(1));
              reporteBean.setComisionPorGestion(rs.getDouble(2));
              reporteBean.setRechazos(rs.getDouble(3));
              listReporte.add(reporteBean);
            }

          }catch (Exception ex){
            log.error(ex, ex);
          }
          
          
          finally
          {
            try
            {
              ConnectionDB.getInstance().closeConnection(cn);
              ConnectionDB.getInstance().closeResultSet(rs);
              ConnectionDB.getInstance().closeStatement(pst);
            }
            catch (SQLException ex)
            {
                log.error(ex.getMessage(), ex);
            }
          }

          return listReporte;
        }
        
        public Map<String,Object> getTotalesGastosBancariosAndDiarios(String fechaLiquidacion){
            
            final int TIPO_MOVIMIENTO = 1;
            final int IMPORTE = 2;
            
            Connection cn = null;
            ResultSet rs = null;
            PreparedStatement pst = null;
            
            StringBuilder query =  new StringBuilder();
            query.append(" SELECT m.nombre,SUM(c.importe) from caja c ");
            query.append(" INNER JOIN movimiento_tipo m ON c.movimiento_tipo_id = m.id ");
            query.append(" WHERE movimiento_tipo_id IN (3,4) ");
            query.append(" AND DATE(c.fecha) = ? ");
            query.append(" AND c.estado = 1 ");
            query.append(" GROUP BY m.nombre;  ");
            
            log.info("query : "+query.toString());
            
            Map<String,Object> mapTotalsResult = new HashMap<>();
            
            try
            {
              cn = ConnectionDB.getInstance().getConnection();
              pst = cn.prepareStatement( query.toString() );
              pst.setString(1, fechaLiquidacion);
              rs = pst.executeQuery();
              
              while (rs.next())
              {
                 mapTotalsResult.put(rs.getString(TIPO_MOVIMIENTO), rs.getString(IMPORTE));
              }
              
              
            } catch (Exception ex){
              log.error(ex, ex);
            }
            
            
            return mapTotalsResult;
            
        } 
        
        
        
        
}
